package com.mes.spc.service;

import com.mes.entity.User;
import com.mes.spc.util.SPCExcelHead;
import com.mes.spc.util.SPCExcelWriteUtil;
import com.mes.spc.util.SpcCalUtil;
import com.yy.base.common.dao.Dao;
import com.yy.base.util.StringUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.math.NumberUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletResponse;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
public class EmesqSpcControlChartService {

	@Autowired
	private Dao dao;
	
	@Autowired
	private EmesqSpcCheckItemConfigService esccs;
	
	public Map<String,Object> checkDataNum(String configId,boolean isSample,String scDate,Map<String,String> paramMap){
		String sqlAdd = isSample?" and sc.IS_SAMPLE='Y' ":" and sc.IS_SAMPLE!='Y'";
		ArrayList<String> param = new ArrayList<String>();
		param.add(configId);
		if(StringUtils.isNotEmpty(scDate)&& scDate.indexOf(" ~ ") > -1){
			String dateFrom = scDate.split(" ~ ")[0];
			String dateTo = scDate.split(" ~ ")[1];
			sqlAdd+= "	and sc.CHECK_DATE >= to_date(?, 'yyyy-MM-dd hh24:mi:ss')"+
					"	and sc.CHECK_DATE <= to_date(?, 'yyyy-MM-dd hh24:mi:ss')";
			param.add(dateFrom);
			param.add(dateTo);
		}
		if(paramMap!=null&&paramMap.size()>0){
			for(String key:paramMap.keySet()){
				if(StringUtils.isNotEmpty(paramMap.get(key))){
					sqlAdd+= "	and sc."+key+" = ?";
					param.add(paramMap.get(key));
				}
			}
		}
		String sql = "select count(1) total,to_char(trunc(max(sc.CHECK_DATE)), 'yyyy-MM-dd hh24:mi:ss') MINDATE,to_char(max(sc.CHECK_DATE),'yyyy-MM-dd hh24:mi:ss') MAXDATE from emesq_spc_sample_calc sc where sc.emesq_spc_ck_config_id=?"+sqlAdd;
		return dao.queryUnique(sql,param.toArray());
		
	}
	public List<Map<String, Object>> getSampleData(Map<String, Object> ckConfig, String configId,String scDate,Map<String,String> paramMap) {
		return getSampleData(ckConfig, configId,false,scDate,paramMap);
	}
	//计量型
	public List<Map<String, Object>> getSampleData(Map<String, Object> ckConfig, String configId,boolean isSample,String scDate,Map<String,String> paramMap) {
		ArrayList<String> param = new ArrayList<String>();
		String sqlAdd = "";
		String sampleSql = isSample?" and sc.IS_SAMPLE='Y' ":" and sc.IS_SAMPLE!='Y'";
		String precision = NumberUtils.toInt("" + ckConfig.get("PRECISION")) + "";
		param.add(precision);
		param.add(precision);
		param.add(precision);
		param.add(precision);
		param.add(precision);
		param.add(configId);
		if(StringUtils.isNotEmpty(scDate)&& scDate.indexOf(" ~ ") > -1){
			String dateFrom = scDate.split(" ~ ")[0];
			String dateTo = scDate.split(" ~ ")[1];
			sqlAdd+= "	and sc.CHECK_DATE >= to_date(?, 'yyyy-MM-dd hh24:mi:ss')"+
					"	and sc.CHECK_DATE <= to_date(?, 'yyyy-MM-dd hh24:mi:ss')";
			param.add(dateFrom);
			param.add(dateTo);
		}
		//过滤掉超出上限值数据
		if(ckConfig.get("VAL_UPPER")!=null&&StringUtils.isNotEmpty(ckConfig.get("VAL_UPPER")+"")){
			sqlAdd+= " and sv.value<=? ";
			param.add(ckConfig.get("VAL_UPPER")+"");
		}
		//过滤掉超出下限值数据
		if(ckConfig.get("VAL_LOWER")!=null&&StringUtils.isNotEmpty(ckConfig.get("VAL_LOWER")+"")){
			sqlAdd+= " and sv.value>=? ";
			param.add(ckConfig.get("VAL_LOWER")+"");
		}
		
		if(paramMap!=null&&paramMap.size()>0){
			for(String key:paramMap.keySet()){
				if(StringUtils.isNotEmpty(paramMap.get(key))){
					sqlAdd+= "	and sc."+key+" = ?";
					param.add(paramMap.get(key));
				}
			}
		}
		
		String sql = "SELECT sc.Emesq_SPC_SAMPLE_CALC_ID, " +
				"       to_char(sc.CHECK_DATE, 'yyyy-MM-dd hh24:mi:ss') checkTime, " + 
				"       sc.CHECK_COUNT, " + 
				"       nvl(sc.STATE,' ') STATE, " + 
				"       sc.IS_CONTROL,  " + 
				"       sc.IS_SAMPLE,  " +
				"       sc.remark1, " +
				"       sc.remark2, " +
				"       sc.remark3, " +
				"       sc.remark4, " +
				"       sc.remark5, " +
				"       round(sc.AVG, ?) AVG," + 
				"       round(decode(sc.Check_Count,1,abs(decode(LAG(sc.AVG,1,0)OVER(ORDER BY sc.CHECK_DATE asc, sc.Emesq_SPC_SAMPLE_CALC_ID asc),0,0,sc.AVG -LAG(sc.AVG,1,0)OVER(ORDER BY sc.CHECK_DATE asc, sc.Emesq_SPC_SAMPLE_CALC_ID asc))),sc.RANGE),?) RANGE, " +
				"       round(sc.STANDARD_DEVIATION, ?) STANDARD_DEVIATION, " + 
				"       round(sc.MAX_VALUE, ?) MAX_VALUE, " + 
				"       round(sc.MIN_VALUE, ?) MIN_VALUE, " + 
				"       to_char(sc.UPDATED, 'yyyy-MM-dd hh24:mi:ss') recTime, " + 
				"       to_char(wm_concat(to_char(sv.VALUE,'fm99990.0099'))) subgroup " + 
				"  FROM Emesq_SPC_SAMPLE_CALC sc, emesq_spc_sample_value sv " +
				"  where sc.emesq_spc_sample_calc_id=sv.emesq_spc_sample_calc_id " + 
				"   and sc.emesq_spc_ck_config_id= ? " + 
				sqlAdd+
				sampleSql+
				"   group by sc.Emesq_SPC_SAMPLE_CALC_ID, sc.CHECK_DATE, " +
				"       sc.CHECK_COUNT, " + 
				"       sc.STATE, " + 
				"       sc.IS_CONTROL, " + 
				"       sc.IS_SAMPLE,  " + 
				"       sc.AVG, " + 
				"       sc.RANGE, " + 
				"       sc.STANDARD_DEVIATION, " + 
				"       sc.MAX_VALUE, " + 
				"       sc.MIN_VALUE, " + 
				"       sc.UPDATED, " +
				"       sc.remark1, " +
				"       sc.remark2, " +
				"       sc.remark3, " +
				"       sc.remark4, " +
				"       sc.remark5 " +
				" having count(1)=sc.CHECK_COUNT " +
				"   order by sc.CHECK_DATE asc,sc.Emesq_SPC_SAMPLE_CALC_ID";
		//System.out.println(sql);
		List<Map<String, Object>> data = this.dao.query(sql, param.toArray());
		for (Map<String, Object> map : data) {
			String[] subgroup = ("" + map.get("SUBGROUP")).split(",");
			for (int i = 0; i < subgroup.length; i++) {
				map.put("SAMPLE_" + (i + 1), subgroup[i]);
			}
		}
		return data;
	}
	
	public Map<String, Object> getBadProductData(Map<String, Object> ckConfig, String configId,String scDate,Map<String,String> paramMap){
		return getBadProductData(ckConfig, configId,false,scDate,paramMap);
	}
	//计数型
	public Map<String, Object> getBadProductData(Map<String, Object> ckConfig, String configId,boolean isSample,String scDate,Map<String,String> paramMap) {
		
		//取不良类别
		List<Map<String, Object>> badTypeList = this.esccs.getBadProductSelData(configId);
		Map<String, Object> badType = new HashMap<String, Object>();
		for (Map<String, Object> map : badTypeList) {
			badType.put(map.get("CODE") + "", map.get("NAME") + "");
		}
		
		String sampleSql = isSample?" and sc.IS_SAMPLE='Y' ":" and sc.IS_SAMPLE!='Y'";
		String sqlAdd = "";
		ArrayList<String> param = new ArrayList<String>();
		param.add(configId);
		//时间筛选
		if(StringUtils.isNotEmpty(scDate)&& scDate.indexOf(" ~ ") > -1){
			String dateFrom = scDate.split(" ~ ")[0];
			String dateTo = scDate.split(" ~ ")[1];
			sqlAdd+= "	and sc.CHECK_DATE >= to_date(?, 'yyyy-MM-dd hh24:mi:ss')"+
					"	and sc.CHECK_DATE <= to_date(?, 'yyyy-MM-dd hh24:mi:ss')";
			param.add(dateFrom);
			param.add(dateTo);
		}
		
		if(paramMap!=null&&paramMap.size()>0){
			for(String key:paramMap.keySet()){
				if(StringUtils.isNotEmpty(paramMap.get(key))){
					sqlAdd+= "	and sc."+key+" = ?";
					param.add(paramMap.get(key));
				}
			}
		}
		
		String sql = "select sc.emesq_spc_sample_calc_id, sc.emesq_spc_ck_config_id, "
				+ "		to_char(sc.CHECK_DATE, 'yyyy-MM-dd hh24:mi:ss') checkTime,"
				+ "		sc.check_count, sc.IS_CONTROL,sc.IS_SAMPLE, nvl(sc.state,' ') state, "
				+ "		to_char(sc.UPDATED, 'yyyy-MM-dd hh24:mi:ss') recTime, " + 
				"       to_char(wm_concat(sv.code || ':' "
				+ "        || (select pr.name from Emesq_QUALITY_POOR_REASON pr where pr.code=sv.code and rownum=1) || ':' || sv.value)) badpro, " +
				"       nvl(sum(sv.value), 0)  badcount, " + 
				"       sc.remark1, " +
				"       sc.remark2, " +
				"       sc.remark3, " +
				"       sc.remark4, " +
				"       sc.remark5 " +
				" FROM Emesq_SPC_SAMPLE_CALC sc left join emesq_spc_sample_value sv  " +
				"    on sc.emesq_spc_sample_calc_id=sv.emesq_spc_sample_calc_id " + 
				"      and sv.code in (";
		for (Map<String, Object> map : badTypeList) {
			badType.put(map.get("CODE") + "", map.get("NAME") + "");
			sql += "'" + map.get("CODE") + "',";
		}
		sql += " '') where sc.emesq_spc_ck_config_id= ?" +
			sampleSql+
			sqlAdd+
			" group by sc.emesq_spc_sample_calc_id, sc.emesq_spc_ck_config_id, sc.check_date, sc.check_count, sc.IS_CONTROL,sc.IS_SAMPLE, sc.state, sc.updated, " + 
			"       sc.remark1, " +
			"       sc.remark2, " +
			"       sc.remark3, " +
			"       sc.remark4, " +
			"       sc.remark5 " +
			" order by check_date asc ";
		
		List<Map<String, Object>> data = this.dao.query(sql, param.toArray());
		
		String  graphType = ckConfig.get("GRAPH_TYPE") + "";
		long badTotal = 0;	//不良总数
		long checkTotal = 0;	//抽检总数
		
		List<Double> badCountList = new ArrayList<Double>();
		List<Double> badRateList = new ArrayList<Double>();
		List<String> calcIdList = new ArrayList<String>();
		
		for (Map<String, Object> map : data) {
			String badPro = map.get("BADPRO") != null ? "" + map.get("BADPRO") : "";
			String[] split = badPro.split(",");
			for (String bad : split) {	//bad="B1:缺角:8"
				String[] val = bad.split(":");
				if(val.length >= 3) {
					map.put("badCode_" + val[0], val[0]);
					map.put("badName_" + val[0], val[1]);
					long count = 0;
					count += NumberUtils.toLong(map.get("badValue_" + val[0]) + "") + NumberUtils.toLong(val[2]);
					map.put("badValue_" + val[0], count);
					badTotal += NumberUtils.toLong(val[2]);
				}
			}
			if("P".equals(graphType) || "U".equals(graphType)) {
				long ckeckCount = NumberUtils.toLong(map.get("CHECK_COUNT") + "");	//抽检数
				if(ckeckCount > 0) {
					double badRate = NumberUtils.toLong(map.get("BADCOUNT") + "") * 100.0 / ckeckCount;
					map.put("badRate", round(badRate, 2));
					badRateList.add(round(NumberUtils.toLong(map.get("BADCOUNT") + "") * 1.0 / ckeckCount, 4));
				}
				checkTotal += ckeckCount;
			}
			badCountList.add(NumberUtils.toDouble(map.get("BADCOUNT") + ""));
			calcIdList.add(map.get("Emesq_SPC_SAMPLE_CALC_ID")+"");
		}
		double cl = 0, ucl = 0, lcl = 0;
		double sigma = 0;
		if(data.size() > 0) {
			if("nP".equals(graphType)) {
				long sampleVol = NumberUtils.toLong("" + ckConfig.get("SAMPLE_VOLUME"), 1);
				cl = badTotal * 1.0 / data.size();
				sigma = Math.sqrt(cl * (1 - cl / sampleVol));
				ucl = cl + 3 * sigma;
				lcl = cl - 3 * sigma;
				if(lcl < 0) {
					lcl = 0;
				}
			}
			if("C".equals(graphType)) {
				cl = badTotal * 1.0 / data.size();
				sigma = Math.sqrt(cl);
				ucl = cl + 3 * sigma;
				lcl = cl - 3 * sigma;
				if(lcl < 0) {
					lcl = 0;
				}
			}
			if("P".equals(graphType)) {
				cl = badTotal * 1.0 / checkTotal;
				for (Map<String, Object> map : data) {
					long ckeckCount = NumberUtils.toLong(map.get("CHECK_COUNT") + "");	//抽检数
					sigma = Math.sqrt(cl * (1 - cl) / ckeckCount);
					ucl = cl + 3 * sigma;
					lcl = cl - 3 * sigma;
					if(lcl < 0) {
						lcl = 0;
					}
					map.put("ucl", round(ucl, 4));
					map.put("lcl", round(lcl, 4));
				}
			}
			if("U".equals(graphType)) {
				cl = badTotal * 1.0 / checkTotal;
				for (Map<String, Object> map : data) {
					long ckeckCount = NumberUtils.toLong(map.get("CHECK_COUNT") + "");	//抽检数
					sigma = Math.sqrt(cl / ckeckCount);
					ucl = cl + 3 * sigma;
					lcl = cl - 3 * sigma;
					if(lcl < 0) {
						lcl = 0;
					}
					map.put("ucl", round(ucl, 4));
					map.put("lcl", round(lcl, 4));
				}
			}
		}
		Map<String, Object> res = new HashMap<String, Object>();
		res.put("sampleData", data);
		res.put("badType", badType);
		res.put("badTotal", badTotal);
		res.put("cl", round(cl, 4));
		res.put("ucl", round(ucl, 4));
		res.put("lcl", round(lcl, 4));
		res.put("rucl", round(cl + 3 * sigma, 4));
		res.put("rlcl", round(cl - 3 * sigma, 4));
		res.put("badCountList", badCountList);
		res.put("badRateList", badRateList);
		res.put("calcIdList", calcIdList);
		return res;
	}
	
	private double round(double d, int scale) {
		BigDecimal b = new BigDecimal(new Double(d).toString());
		b = b.setScale(scale, BigDecimal.ROUND_HALF_UP);
		return b.doubleValue();
	}

	public Map<String,Object> getSampleArrayData(Map<String, Object> ckConfig, String configId,String scDate,Map<String,String> paramMap) {
		return getSampleArrayData(ckConfig, configId,false,scDate,paramMap);
	}
	
	/**
	 * 获取计量型样本数据
	 * @param ckConfig
	 * @param configId
	 * @param isSample
	 * @return
	 */
	public Map<String,Object> getSampleArrayData(Map<String, Object> ckConfig, String configId,boolean isSample,String scDate,Map<String,String> paramMap) {
		ArrayList<String> param = new ArrayList<String>();
		ArrayList<String> param2 = new ArrayList<String>();
		param2.add(configId);
		String precision = NumberUtils.toInt("" + ckConfig.get("PRECISION")) + "";
		param.add(precision);
		param.add(precision);
		param.add(precision);
		param.add(precision);
		param.add(precision);
		param.add(configId);
		String sapmpleSql = isSample?" and IS_SAMPLE='Y' ":" and sc.IS_SAMPLE!='Y'";
		String sqlAdd = "";
		if(StringUtils.isNotEmpty(scDate)&& scDate.indexOf(" ~ ") > -1){
			String dateFrom = scDate.split(" ~ ")[0];
			String dateTo = scDate.split(" ~ ")[1];
			sqlAdd+= "	and sc.CHECK_DATE >= to_date(?, 'yyyy-MM-dd hh24:mi:ss')"+
					"	and sc.CHECK_DATE <= to_date(?, 'yyyy-MM-dd hh24:mi:ss')";
			param.add(dateFrom);
			param.add(dateTo);
			
			param2.add(dateFrom);
			param2.add(dateTo);
		}
		
		//过滤数据sql
		String rangeSql = " and (select count(1) from emesq_spc_sample_value where 1=1 ";
		//过滤掉超出上限值数据
		if(ckConfig.get("VAL_UPPER")!=null&&StringUtils.isNotEmpty(ckConfig.get("VAL_UPPER")+"")){
			rangeSql+= " and value<=? ";
			param.add(ckConfig.get("VAL_UPPER")+"");
			param2.add(ckConfig.get("VAL_UPPER")+"");
		}
		//过滤掉超出下限值数据
		if(ckConfig.get("VAL_LOWER")!=null&&StringUtils.isNotEmpty(ckConfig.get("VAL_LOWER")+"")){
			rangeSql+= " and value>=? ";
			param.add(ckConfig.get("VAL_LOWER")+"");
			param2.add(ckConfig.get("VAL_LOWER")+"");
		}
		rangeSql += " and Emesq_SPC_SAMPLE_CALC_ID = sc.emesq_spc_sample_calc_id " +
				" group by Emesq_SPC_SAMPLE_CALC_ID) = sc.check_count ";
		sqlAdd += rangeSql;
		if(paramMap!=null&&paramMap.size()>0){
			for(String key:paramMap.keySet()){
				if(StringUtils.isNotEmpty(paramMap.get(key))){
					sqlAdd+= "	and sc."+key+" = ?";
					param.add(paramMap.get(key));
					param2.add(paramMap.get(key));
				}
			}
		}
		//获取样本数据
		String sql = "SELECT sc.Emesq_SPC_SAMPLE_CALC_ID, " +
				"       to_char(sc.CHECK_DATE, 'yyyy-MM-dd hh24:mi:ss') checkTime, " +
				"       sc.CHECK_COUNT, " +
				"       sc.STATE, " +
				"       sc.IS_CONTROL,  " +
				"       round(sc.AVG, ?) AVG," +
				"       round(decode(sc.Check_Count,1,abs(decode(LAG(sc.AVG,1,0)OVER(ORDER BY sc.CHECK_DATE asc, sc.Emesq_SPC_SAMPLE_CALC_ID asc),0,0,sc.AVG -LAG(sc.AVG,1,0)OVER(ORDER BY sc.CHECK_DATE asc, sc.Emesq_SPC_SAMPLE_CALC_ID asc))),sc.RANGE),?) RANGE, " +
				"       round(sc.STANDARD_DEVIATION, ?) STANDARD_DEVIATION, " +
				"       round(sc.MAX_VALUE, ?) MAX_VALUE, " +
				"       round(sc.MIN_VALUE, ?) MIN_VALUE, " +
				"       to_char(sc.UPDATED, 'yyyy-MM-dd hh24:mi:ss') recTime" +
				"  FROM Emesq_SPC_SAMPLE_CALC sc" +
				"  where 1=1 " +
				sapmpleSql+
				"   and sc.emesq_spc_ck_config_id= ? "+
				sqlAdd+
				" order by sc.CHECK_DATE,sc.Emesq_SPC_SAMPLE_CALC_ID";
		//获取所有组内样本数据
		String sql2 = "SELECT sv.VALUE oneDataValue" +
				"  FROM Emesq_SPC_SAMPLE_CALC sc, emesq_spc_sample_value sv " +
				"  where sc.emesq_spc_sample_calc_id=sv.emesq_spc_sample_calc_id " +
				sapmpleSql+
				"   and sc.emesq_spc_ck_config_id= ? "+
				sqlAdd
				;
//System.out.println(sql);
		List<Map<String, Object>> data = this.dao.query(sql, param.toArray());
		
		
		List<Map<String, Object>> oneDataValueList = this.dao.query(sql2,param2.toArray());

		Double[] arrData = new Double[oneDataValueList.size()];//所有样本值
		Double[] avgArray = new Double[data.size()];//样本均值
		Double[] avgRArray = new Double[data.size()];//极差平均值
		Double[] avgStdevArray = new Double[data.size()];//方差均值
		String[] calcIdArray = new String[data.size()];//样本id
		String[] stateArray = new String[data.size()];//状态数组
		for (int i=0;i<data.size();i++) {
			avgArray[i] = Double.parseDouble(data.get(i).get("AVG")+"");
			avgRArray[i] = Double.parseDouble(data.get(i).get("RANGE")+"");
			avgStdevArray[i] = Double.parseDouble(data.get(i).get("STANDARD_DEVIATION")+"");
			calcIdArray[i] = data.get(i).get("Emesq_SPC_SAMPLE_CALC_ID")+"";
			stateArray[i] = data.get(i).get("STATE")+"";
		}
		for (int i=0;i<oneDataValueList.size();i++) {
			arrData[i] = Double.parseDouble(oneDataValueList.get(i).get("ONEDATAVALUE")+"");
		}
		Map<String,Object> dataMap = new HashMap<String, Object>();
		dataMap.put("arrData",arrData);
		dataMap.put("avgArray",avgArray);
		dataMap.put("avgRArray",avgRArray);
		dataMap.put("avgStdevArray",avgStdevArray);
		dataMap.put("dataSize",arrData.length);
		dataMap.put("calcIdArray", calcIdArray);
		dataMap.put("stateArray", stateArray);
		return dataMap;
	}
	/**
	 * 新增数据
	 * @param configId
	 * @param checkDate
	 * @param checkCount
	 * @param sampleDataStr
	 * @param badTypeCodeStr
	 * @param checkItemType
	 * @param graphType
	 * @param isSample
	 * @param user
	 * @param orgId
	 * @return
	 * @throws Exception
	 */
	public boolean addData(String configId,String checkDate,String checkCount,String sampleDataStr,String badTypeCodeStr,String checkItemType,String graphType,String isSample,User user,String orgId) throws Exception{
		boolean flag = false;
		String getCalId = StringUtil.getUUID();
		String[] sampleDataStrArray =  sampleDataStr.split(",");
		double[] sampleData = new double[sampleDataStrArray.length];
		
		for(int i = 0;i< sampleDataStrArray.length;i++){
			sampleData[i] = Double.parseDouble(sampleDataStrArray[i]);
		}
		double avgNum = SpcCalUtil.avage(sampleData);
		double stdNum = sampleDataStrArray.length==1?0:SpcCalUtil.stDev(sampleData);
		double maxNum = SpcCalUtil.max(sampleData);
		double minNum = SpcCalUtil.min(sampleData);
		double rNum = maxNum-minNum;
		if(checkItemType.equals("variables")){//计量型
			int r1 = esccs.insertSampleCalcForVar(getCalId, configId, checkDate, user, orgId, avgNum, stdNum, maxNum, minNum, rNum, isSample,Integer.parseInt(checkCount));
			int r2 = esccs.insertSampleCalcValueForVar(getCalId, sampleData, user, orgId);
			if(graphType.equals("IMR")){
				//单值图，需要重新根据顺序进行计算极差值
				calcImrRVal(configId);
			}
			flag = r1>0&&r2>0;
		}else{//计数型
			String[] badTypeCodeStrArray = badTypeCodeStr.split(",");//不良编码
			int r1 = esccs.insertSampleCalcForCount(getCalId, configId, checkDate, user, orgId, checkCount, isSample);
			int r2 = esccs.insertSampleCalcValueForCount(getCalId, sampleDataStrArray, badTypeCodeStrArray, user, orgId);
			flag = r1>0&&r2>0;
		}
		//重新执行判异规则
		if(flag){
			//esccs.executeCheckRule(configId,user,orgId,"");
		}
		return flag;
	}
	
	/**
	 * 删除单个数据
	 * @param calcId
	 * @return
	 * @throws Exception 
	 */
	public boolean delCalData(String configId,String calcId,User user,String orgId) throws Exception{
		boolean flag = esccs.deleteSampleValueData(calcId);
		if(flag){
			//esccs.executeCheckRule(configId,user,orgId,"");
		}
		return flag;
	}
	
	public boolean editData(String configId,String curEditCalcId,String checkDate,String checkCount,String sampleDataStr,String badTypeCodeStr,String checkItemType,String graphType,String isSample,User user,String orgId) throws Exception{
		//进行原始数据删除
		esccs.deleteSampleValueData(curEditCalcId);
		
		boolean flag = false;
		String getCalId = StringUtil.getUUID();
		String[] sampleDataStrArray =  sampleDataStr.split(",");
		double[] sampleData = new double[sampleDataStrArray.length];
		Map<String,Object> configMap =  esccs.getSpcCkConfigById(configId);
		
		for(int i = 0;i< sampleDataStrArray.length;i++){
			sampleData[i] = Double.parseDouble(sampleDataStrArray[i]);
		}
		double avgNum = SpcCalUtil.avage(sampleData);
		double stdNum = sampleDataStrArray.length==1?0:SpcCalUtil.stDev(sampleData);
		double maxNum = SpcCalUtil.max(sampleData);
		double minNum = SpcCalUtil.min(sampleData);
		double rNum = maxNum-minNum;
		if(checkItemType.equals("variables")){//计量型
			checkCount = configMap.get("SAMPLE_VOLUME")+"";
			int r1 = esccs.insertSampleCalcForVar(getCalId, configId, checkDate, user, orgId, avgNum, stdNum, maxNum, minNum, rNum, isSample,Integer.parseInt(checkCount));
			int r2 = esccs.insertSampleCalcValueForVar(getCalId, sampleData, user, orgId);
			if(graphType.equals("IMR")){
				//单值图，需要重新根据顺序进行计算极差值
				calcImrRVal(configId);
			}
			flag = r1>0&&r2>0;
		}else{//计数型
			String[] badTypeCodeStrArray = badTypeCodeStr.split(",");//不良编码
			int r1 = esccs.insertSampleCalcForCount(getCalId, configId, checkDate, user, orgId, checkCount, isSample);
			int r2 = esccs.insertSampleCalcValueForCount(getCalId, sampleDataStrArray, badTypeCodeStrArray, user, orgId);
			flag = r1>0&&r2>0;
		}
		//重新执行判异规则
		if(flag){
			//esccs.executeCheckRule(configId,user,orgId,"");
		}
		return flag;
	}
	
	private void calcImrRVal(String configId){
		String sql = "select essc.emesq_spc_sample_calc_id CALCID,essc.avg from emesq_spc_sample_calc essc "+ 
					" where essc.emesq_spc_ck_config_id=? "+
					" order by essc.check_date asc";
		List<Map<String, Object>> rs = dao.query(sql, configId);
		int jrCount = 0;
		double tempLastVal = 0;
		if(rs!=null&&rs.size()>0){
			for(Map<String, Object> m:rs){
				double rNum;
				if (jrCount == 0) {
					rNum = 0;
				} else {
					rNum = Math.abs(tempLastVal - Double.parseDouble(m.get("AVG")+""));
				}
				tempLastVal = Double.parseDouble(m.get("AVG")+"");//记录上一个值
				jrCount++;
				//更新极差值
				String usql = "update emesq_spc_sample_calc set RANGE=? where Emesq_SPC_SAMPLE_CALC_ID=?";
				dao.executeSql(usql, rNum,m.get("CALCID"));
			}
		}
	}
	
	public void doExportExcel(String configId,boolean isSample,String scDate,Map<String,String> paramMap,HttpServletResponse response){
		try {
			Map<String, Object> ckConfig = esccs.getSpcCkConfigById(configId);
			if(ckConfig==null){
				throw new Exception("检测项目ID错误！");
			}
			String checkItemType = ckConfig.get("CHECKITEM_TYPE")+"";//检测类型 variables 计量  counting  计数
			String graphType = ckConfig.get("GRAPH_TYPE")+"";//图表类型
			String code = ckConfig.get("CODE")+"";//编码
			SPCExcelHead[][] heads = null;
			String fileName = "";
			SPCExcelWriteUtil util = new SPCExcelWriteUtil();
			util.createExcel();
			if(graphType.equals("XR")||graphType.equals("XS")||graphType.equals("MR")||graphType.equals("IMR")) {
				if(ckConfig.get("SAMPLE_VOLUME")==null)throw new Exception("未设置样本容量！");
				int sampleSize = Integer.parseInt(ckConfig.get("SAMPLE_VOLUME")+"");//样本数量
				fileName = "计量模板";
				heads = new SPCExcelHead[1][sampleSize+9];
				heads[0][0] = new SPCExcelHead("抽检时间");//固定列
				heads[0][1] = new SPCExcelHead("设备");//固定列
				heads[0][2] = new SPCExcelHead("位置");//固定列
				heads[0][3] = new SPCExcelHead("管号");//固定列
				for (int j = 1; j <= sampleSize; j++) {
					heads[0][3+j] = new SPCExcelHead("样本"+j);
				}
				heads[0][3+sampleSize+1] = new SPCExcelHead("平均值");
				heads[0][3+sampleSize+2] = new SPCExcelHead("极差");
				heads[0][3+sampleSize+3] = new SPCExcelHead("标准差");
				heads[0][3+sampleSize+4] = new SPCExcelHead("最大值");
				heads[0][3+sampleSize+5] = new SPCExcelHead("最小值");
				util.createHead(heads);
				
				List<Map<String, Object>> datas = getSampleData(ckConfig, configId, isSample, scDate, paramMap);
				//插入数据
				for(Map<String, Object> d:datas){
					List<String> tempList = new ArrayList<String>();
					String checkTime = d.get("CHECKTIME")+"";
					String remark1 = d.get("REMARK1")!=null?(d.get("REMARK1")+""):"";
					String remark2 = d.get("REMARK2")!=null?(d.get("REMARK2")+""):"";
					String remark3 = d.get("REMARK3")!=null?(d.get("REMARK3")+""):"";
					String remark4 = d.get("REMARK4")!=null?(d.get("REMARK4")+""):"";
					String remark5 = d.get("REMARK5")!=null?(d.get("REMARK5")+""):"";
					tempList.add(checkTime);
					tempList.add(remark1);
					tempList.add(remark2);
					tempList.add(remark3);
					for(int i=1;i<=sampleSize;i++){
						tempList.add(d.get("SAMPLE_"+i)+"");
					}
					tempList.add(d.get("AVG")+"");
					tempList.add(d.get("RANGE")+"");
					tempList.add(d.get("STANDARD_DEVIATION")+"");
					tempList.add(d.get("MAX_VALUE")+"");
					tempList.add(d.get("MIN_VALUE")+"");
					util.addRow(tempList.toArray(new String[0]));
				}
			}else{
				fileName = "计数模板";
				String sql = "select r.name RNAME,r.code RCODE from Emesq_SPC_BADPRODUCT t,Emesq_QUALITY_POOR_REASON r " +
						"where 1=1 " +
						"and t.emesq_quality_poor_reason_id=r.emesq_quality_poor_reason_id " +
						"and t.emesq_spc_ck_config_id=?";
				List<Map<String, Object>> cols = dao.query(sql,configId);//不良列
				heads = new SPCExcelHead[1][cols.size()+2];
				heads[0][0] = new SPCExcelHead("抽检时间");//固定列
				heads[0][1] = new SPCExcelHead("抽检数");//固定列
				for(int i=0;i<cols.size();i++){
					heads[0][i+2] = new SPCExcelHead(cols.get(i).get("RNAME")+"");
				}
				util.createHead(heads);
				Map<String, Object> dataMap = getBadProductData(ckConfig, configId, isSample, scDate, paramMap);
				List<Map<String, Object>> datas = (List<Map<String, Object>>)dataMap.get("sampleData");
				if(datas!=null&&datas.size()>0){
					for(Map<String, Object> d:datas){
						List<String> tempList = new ArrayList<String>();
						tempList.add(d.get("CHECKTIME")+"");
						tempList.add(d.get("CHECK_COUNT")+"");
						String BADPRO = d.get("BADPRO")+"";
						if(d.get("BADPRO")!=null){
							for(int i=0;i<cols.size();i++){
								tempList.add(d.get("badValue_"+cols.get(i).get("RCODE"))+"");
							}
						}
						util.addRow(tempList.toArray(new String[0]));
					}
				}
			}
			fileName+="，编码为："+code+"，图表类型："+graphType;
			util.writeFileToResponse(response,fileName+"—"+new SimpleDateFormat("yyyyMMddhhmmss").format(new Date()).toString());
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
}
